
/*****************************************************************************************
	NTirety Server Audit Script 
	CAC	5/11/2005	Created
	CAC	3/9/2006	Updated
*****************************************************************************************/
set nocount on

use master

/****************************************************************************************
Server Config Summary
****************************************************************************************/
print getdate()
print 'Server: ' + @@SERVERNAME
print 'Instance: ' + @@SERVICENAME
   
select @@version
select 'SQLServer Started On ' = login_time from master..sysprocesses where spid = 1
exec master..xp_msver

print 'Number of connections..: ' + convert(varchar(30),@@connections)        
print 'Language...............: ' + convert(varchar(30),@@language)          
print 'Language Id............: ' + convert(varchar(30),@@langid)            
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)      
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)   
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)        
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)
print ''

--exec sp_configure 'show advanced options',1
exec sp_configure

--Some SQL Server Perf Counters (I Can add to this...)
select 	distinct counter_name,
	(select isnull(sum(convert(dec(15,0),B.cntr_value)),0) 
	from 	master..sysperfinfo as B (nolock) 
	where 	Lower(B.counter_name) like '%hit ratio%'
	and	A.counter_name = B.counter_name) as CurrHit,
	(select isnull(sum(convert(dec(15,0),B.cntr_value)),0) 
	from 	master..sysperfinfo as B (nolock) 
	where 	Lower(B.counter_name) like '%hit ratio base%'
	and	lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,
	(select isnull(sum(convert(dec(15,0),B.cntr_value)),0) 
	from 	master..sysperfinfo as B (nolock) 
	where 	Lower(B.counter_name) like '%hit ratio%'
	and	A.counter_name = B.counter_name) / 
	(select isnull(sum(convert(dec(15,0),B.cntr_value)),0) 
	from 	master..sysperfinfo as B (nolock) 
	where 	Lower(B.counter_name) like '%hit ratio base%'
	and	lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio
from 	master..sysperfinfo as A (nolock) 
where 	Lower(A.counter_name) like '%hit ratio%'
and 	Lower(A.counter_name) not like '%hit ratio base%' 

-- Audit list as a double verification
select counter_name, cast(isnull(sum(convert(dec(15,0),cntr_value)),0) as int) as Value	-- JE /1024
from 	master..sysperfinfo (nolock) 
where 	Lower(counter_name) like '%hit ratio%'
or 	Lower(counter_name) like '%Server Memory%'
group by counter_name

print 'Linked/Remote Servers.... '
exec sp_helplinkedsrvlogin
exec sp_helpremotelogin

/*****************************************************************************************
Db Config Summary
******************************************************************************************/
--Check to make sure all db's have a valid owner.
print ''
select 'Databases without a valid owner'=name
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
and databasepropertyex(name,'status') = 'ONLINE'	-- JE
and has_dbaccess(name)=1
--To fix: sp_changedbowner 'sa'

--If the above returns any rows, sp_helpdb will return errors
-- exec sp_helpdb

SELECT LEFT(name,30) AS DB, 
        SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END + 
        CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END + 
        CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END + 
        CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END + 
        CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END + 
        CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END + 
        CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END + 
        CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END + 
        CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END + 
        CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END + 
        CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END + 
        CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END + 
        CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END + 
        CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END + 
        CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END + 
        CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END + 
        CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END + 
        CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END + 
        CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END + 
        CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END + 
        CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END + 
        CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END + 
        CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END + 
        CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END, 
2,8000) AS Descr 
FROM master..sysdatabases 

print ''
-- dbcc sqlperf(logspace) with no_infomsgs
-- sql2005,2008
set nocount on set implicit_transactions off
if object_id('tempdb.dbo.#logspace','U') is not null drop table #logspace
create table #logspace (name sysname, [Log Size(MB)] float, [Log Space Used (%)] float, [Status] int )
insert #logspace exec('DBCC SQLPERF(logspace) with no_infomsgs')
select convert(varchar(31),l.name) as name, convert(varchar(12),databasepropertyex(l.name,'status')) as [status], convert(varchar(16),databasepropertyex(l.name,'recovery')) as [recovery model]
, ( select count(*) as [data files] from sys.master_files where db_name(database_id) = l.name and type=0 group by database_id ) as [data files]
, db.[log files]
, convert(numeric(11,1),l.[Log Size(MB)]) as [log size (mb)], convert(int,[Log Space Used (%)]) as [log % used] 
, db.database_id, convert(nvarchar(11),d.create_date) as create_date, d.compatibility_level
from #logspace l join ( select database_id,count(*) as [log files] from sys.master_files group by database_id, type having type=1 ) as db on l.name = db_name(db.database_id)
join sys.databases d on d.database_id = db.database_id
if object_id('tempdb.dbo.#logspace','U') is not null drop table #logspace

go

----------------------------------------------------------------------------------------------------------

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats ;
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))
DROP TABLE #TempForDataFile ;
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))
DROP TABLE #TempForLogFile ;

/*
DECLARE @DBName nvarchar(30)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE status&512 = 0 and databasepropertyex(name,'status')='ONLINE'

CREATE TABLE #TempForFileStats([Server Name]          sysname,
                               [Database Name]        sysname,
                               [File Name]            nvarchar(128),
                               [Usage Type]           varchar (6),
                               [Size (MB)]            real, 
                               [Space Used (MB)]      real,
                               [MaxSize (MB)]         real,
                               [Next Allocation (MB)] real, 
                               [Growth Type]          varchar (12),
                               [File Id]              smallint,
                               [Group Id]             smallint,
                               [Physical File]        nvarchar (260),
                               [Date Checked]         datetime) 

CREATE TABLE #TempForDataFile ([File Id]             smallint,
                               [Group Id]            smallint,
                               [Total Extents]       int,
                               [Used Extents]        int,
                               [File Name]           nvarchar(128),
                               [Physical File]       nvarchar(260))

CREATE TABLE #TempForLogFile  ([File Id]             int, 
                               [Size (Bytes)]        real, 
                               [Start Offset]        varchar(30), 
                               [FSeqNo]              int, 
                               [Status]              int, 
                               [Parity]              smallint, 
                               [CreateTime]          varchar(30))   

OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @SQLString = 'SELECT @@SERVERNAME                     as  ''ServerName'', '          + 
                       '''[' + @DBName + ']''' + '                as  ''Database'', '            +  
                       '        f.name, '                                                      +
                       '       CASE '                                                          +
                       '          WHEN (64 & f.status) = 64 THEN ''Log'' '                     +
                       '          ELSE ''Data'' '                                              + 
                       '       END                              as ''Usage Type'', '           +
                       '        f.size*8/1024.00                as ''Size (MB)'', '            +
                       '        NULL                            as ''Space Used (MB)'', '      +
                       '        CASE f.maxsize '                                               +
                       '           WHEN -1 THEN  -1 '                                        +
                       '           WHEN  0 THEN  f.size*8/1024.00  '                           +
                       '           ELSE          f.maxsize*8/1024.00 '                         +
                       '        END                             as ''Max Size (MB)'', '        +
                       '        CASE '                                                         +
                       '           WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) ' + 
                       '           WHEN f.growth =0                 THEN 0 '                +
                       '           ELSE                                   f.growth*8/1024.00 ' +
                       '        END                             as ''Next Allocation (MB)'', ' +
                       '       CASE  '                                                         +
                       '          WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' '      +
                       '          ELSE ''Pages'' '                                             +
                       '       END                              as ''Usage Type'', '           +
                       '       f.fileid, '                                                     +
                       '       f.groupid, '                                                    +
                       '       filename, '                                                     +
                       '       getdate() '                                                     +
                       ' FROM [' + @DBName + '].dbo.sysfiles f' 

      INSERT #TempForFileStats 
      EXECUTE(@SQLString)
      ------------------------------------------------------------------------
      SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS  with no_infomsgs'
      INSERT #TempForDataFile
      EXECUTE(@SQLString)

      --
      UPDATE #TempForFileStats
      SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
      FROM #TempForFileStats f,
           #TempForDataFile  s
      WHERE f.[File Id]       = s.[File Id]
        AND f.[Group Id]      = s.[Group Id]
        AND f.[Database Name] = '[' + @DBName + ']'

      --
      TRUNCATE TABLE #TempForDataFile
      -------------------------------------------------------------------------
      SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO  with no_infomsgs'
      INSERT #TempForLogFile
      EXECUTE(@SQLString)      
      --
      UPDATE #TempForFileStats 
      SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) + 
                                       SUM(CASE 
                                              WHEN l.Status <> 0 THEN  l.[Size (Bytes)] 
                                              ELSE           0 
                                           END))/1048576.00
                               FROM #TempForLogFile l
                               WHERE l.[File Id] = f.[File Id])
      FROM #TempForFileStats f
      WHERE f.[Database Name] = '[' + @DBName + ']'
        AND f.[Usage Type]    = 'Log'
      --
      TRUNCATE TABLE #TempForLogFile 
      -------------------------------------------------------------------------
      FETCH NEXT FROM c_db INTO @DBName
   END
DEALLOCATE c_db

SELECT * FROM #TempForFileStats
------------
DROP TABLE #TempForFileStats 
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
*/
go
----------------------------------------------------------------------------------------------------------

set nocount on
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TBL_DATABASEFILES'))
   DROP TABLE #TBL_DATABASEFILES
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TBL_FILESTATISTICS'))
   DROP TABLE #TBL_FILESTATISTICS
/*
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(500)

DECLARE @MTB_DATABASES TABLE ( 
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )

CREATE TABLE  #TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
DATABASENAME SYSNAME)

INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES 
	where databasepropertyex(name,'status') = 'ONLINE'	-- JE
ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
   SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
   SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME,DATABASENAME) SELECT '+STR(@INT_DBID)+',FILEID,NAME,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
   EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
   SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....

CREATE TABLE #TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)

SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
   SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
   INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
   SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
   SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
select * from #TBL_FILESTATISTICS

drop table #TBL_DATABASEFILES ;
drop table #TBL_FILESTATISTICS
*/
go

---------------------------------------------------------------------------------------

select 	SUBSTRING(s.name,1,40)			AS	'Database',
	CAST(b.backup_start_date AS char(11)) 	AS 	'Backup Date  ',
	CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
		THEN 'Backup is current within a day'
	     WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
		THEN 'Backup is current within a week'
	     ELSE '*****CHECK BACKUP!!!*****'
		END
						AS 'Comment'

from 	master..sysdatabases	s
LEFT OUTER JOIN	msdb..backupset b
	ON s.name = b.database_name
	AND b.backup_start_date = (SELECT MAX(backup_start_date)
					FROM msdb..backupset
					WHERE database_name = b.database_name
						AND type = 'D')		-- full database backups only, not log backups
WHERE	s.name <> 'tempdb'

ORDER BY 	s.name

/*****************************************************************************************
Logins/User Summary
******************************************************************************************/
exec sp_helplogins
exec sp_helpsrvrolemember

print 'List of special users per database...'
declare @name sysname,
	@SQL  nvarchar(600)

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
	drop table #tmpTable
	
CREATE TABLE #tmpTable (
	[DATABASE_NAME] sysname NOT NULL ,
	[USER_NAME] sysname NOT NULL,
	[ROLE_NAME] sysname NOT NULL)

declare c1 cursor for 
	select name from master.dbo.sysdatabases
		where databasepropertyex(name,'status') = 'ONLINE'
			
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
	select @SQL = 
		'insert into #tmpTable
		 select N'''+ @name + ''', a.name, c.name
		from ' + QuoteName(@name) + '.dbo.sysusers a 
		join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
		join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
		where a.name != ''dbo'''

		/* 	Insert row for each database */
		execute (@SQL)
	fetch c1 into @name
end
close c1
deallocate c1
	
select * from #tmpTable
drop table #tmpTable

/***********************************************************************
Misc Summary (jobs, sp_who, locks)
***********************************************************************/
exec msdb..sp_help_job
exec sp_who2
--exec sp_lock

set nocount off

--Get disk space
execute master..xp_fixeddrives

/***********************************************************************/

set nocount on

Print '********** Exec Summary Starts Here **********'
Print '----------------------------------------------'
Print 'Audit Date: ' + cast(getdate() as varchar(20))
Print ''
Print 'Server: ' + @@SERVERNAME
Print 'Instance: ' + @@SERVICENAME
Print 'Product Version: ' + @@version

exec master..xp_msver 'ProcessorCount', 'ProcessorType', 'PhysicalMemory'

print 'Total Read: ' + convert(varchar(30),@@TOTAL_READ)
print 'Total Write: ' + convert(varchar(30),@@TOTAL_WRITE)
print ''

select 	distinct left(counter_name,30),
	(select isnull(sum(convert(dec(15,0),B.cntr_value)),0) 
	from 	master..sysperfinfo as B (nolock) 
	where 	Lower(B.counter_name) like '%hit ratio%'
	and	A.counter_name = B.counter_name) / 
--	(select isnull(sum(convert(dec(15,0),Bef.cntr_value)),0)	JE
	(select isnull(sum(convert(dec(15,0),cntr_value)),0) 
	from 	master..sysperfinfo as B (nolock) 
	where 	Lower(B.counter_name) like '%hit ratio base%'
	and	lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base'))
from 	master..sysperfinfo as A (nolock) 
where 	Lower(A.counter_name) like '%hit ratio%'
and 	Lower(A.counter_name) not like '%hit ratio base%' 

set nocount off


-- JE

use [master] ;
WITH DBIO AS
      (
        SELECT
            left(DB_NAME(IVFS.database_id),50) AS db,
	left(MF.name,43) as name, MF.file_id ,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
             SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
            SUM(IVFS.io_stall) AS io_stall,
left(MF.physical_name,47) as physical_name
        FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
            JOIN sys.master_files AS MF
              ON IVFS.database_id = MF.database_id
              AND IVFS.file_id = MF.file_id
        GROUP BY DB_NAME(IVFS.database_id), MF.type, MF.name, MF.physical_name, MF.file_id 
      )
      SELECT db, name, physical_name, file_id, file_type, 
        CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
        CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_ss,
        CAST(100. * io_stall / SUM(io_stall) OVER()
               AS DECIMAL(10, 2)) AS io_stall_pct
        --, ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
      FROM DBIO
      ORDER BY io_stall DESC, io_mb desc ;

